package com.group.zsxm.service;

import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.springframework.stereotype.Service;

import com.group.core.common.Page;
import com.group.zsxm.exception.BusException;
import com.group.zsxm.service.common.BaseService;

@Service
public class ExcelService extends BaseService{

	public void doExportDwjfh(OutputStream os,String dwid,String dwmc,String d){
		WritableWorkbook wwb = null;
		
		try{
			//创建 Excel 
			wwb = Workbook.createWorkbook(os);
			WritableSheet ws = wwb.createSheet("经费资助",0);
			ws.getSettings().setOrientation(PageOrientation.LANDSCAPE); //横向打印
			
			jxl.write.Label labelC = null;
			Number labelNF = null;
			
			jxl.write.WritableFont wfcT = new jxl.write.WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    	    jxl.write.WritableCellFormat wcfFCT = new jxl.write.WritableCellFormat(wfcT);
    	    wcfFCT.setAlignment(Alignment.CENTRE); //水平居中
    	    wcfFCT.setBorder(Border.ALL, BorderLineStyle.THIN);
    	    wcfFCT.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
    	    
    	    
    	    jxl.write.WritableFont wfcT_ = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    	    jxl.write.WritableCellFormat wcfFC_ = new jxl.write.WritableCellFormat(wfcT_);
    	    wcfFC_.setBorder(Border.ALL, BorderLineStyle.THIN);
    	    
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    	    jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
    	    wcfFC.setAlignment(Alignment.CENTRE);
    	    wcfFC.setWrap(true);//自动换行
    	    wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
    	    wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
    	    
    	    jxl.write.WritableFont wfcc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    	    jxl.write.WritableCellFormat wcfFCC = new jxl.write.WritableCellFormat(wfcc);
    	    wcfFCC.setWrap(true);
    	    wcfFCC.setBorder(Border.ALL, BorderLineStyle.THIN);
    	    
    	    //数值
		    jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");
		    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
		    wcfN.setBorder(Border.ALL, BorderLineStyle.THIN);
		    
		    int mr = 0;
		    ws.mergeCells(mr, 0, mr+12, 0);//标题
		    ws.mergeCells(mr, 1, mr+12, 1);//副标题
		    ws.mergeCells(mr, 2, mr, 3); //字段名
		    ws.mergeCells(mr+1, 2, mr+1, 3); //字段名
		    ws.mergeCells(mr+2, 2, mr+2, 3); //字段名
		    ws.mergeCells(mr+3, 2, mr+3, 3); //字段名
		    ws.mergeCells(mr+4, 2, mr+6, 2); //字段名
		    ws.mergeCells(mr+7, 2, mr+10, 2); //字段名
		    ws.mergeCells(mr+11, 2, mr+11, 3); //字段名
		    ws.mergeCells(mr+12, 2, mr+12, 3); //字段名
		    
		    int row = 0;
		    
		    labelC = new Label(mr,row,"金凤凰高层次人才”经费资助（奖励）登记表",wcfFCT);
	    	ws.addCell(labelC);
	    	
	    	++row;
	    	labelC = new Label(mr,row,"单位：（盖章） "+dwmc+"                                                                                                "+d,wcfFC_);
	    	ws.addCell(labelC);
	    	
	    	++row;
	    	labelC = new Label(mr,row,"序号",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(0, 3);
	    	
	    	labelC = new Label(mr+1,row,"姓名",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+1, 10);
	    	
	    	labelC = new Label(mr+2,row,"学历/职称",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+2, 10);
	    	
	    	labelC = new Label(mr+3,row,"专/兼职",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+3, 10);
	    	
	    	labelC = new Label(mr+4,row,"生活资助经费",wcfFC);
	    	ws.addCell(labelC);
	    	
	    	labelC = new Label(mr+7,row,"住房补贴费",wcfFC);
	    	ws.addCell(labelC);
	    	
	    	labelC = new Label(mr+11,row,"安家费补贴总额",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+11, 11);
	    	
	    	labelC = new Label(mr+12,row,"资助总额(元)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+12, 11);
	    	
	    	++row;
	    	labelC = new Label(mr+4,row,"资助标准(元/月)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+4, 10);
	    	
	    	labelC = new Label(mr+5,row,"在常时间(月)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+5, 10);
	    	
	    	
	    	labelC = new Label(mr+6,row,"资助总额(元)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+6, 10);
	    	
	    	
	    	labelC = new Label(mr+7,row,"住宿地(城内/城外)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+7, 10);
	    	
	    	labelC = new Label(mr+8,row,"补贴标准(元/月)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+8, 10);
	    	
	    	labelC = new Label(mr+9,row,"在常时间(月)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+9, 10);
	    	
	    	labelC = new Label(mr+10,row,"资助总额(元)",wcfFC);
	    	ws.addCell(labelC);
	    	ws.setColumnView(mr+10, 10);
	    	
	    	
			Map jfhsb = new HashMap();
			String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
			if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
				sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
				jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
			}else{
				throw  new BusException("当前填报已结束，不能导出!");
			}
			String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
			
			sql = "select a.RYID,a.XM,a.SFZ,a.SEX_MC,isnull(a.XL_MC,' ') XL_MC,a.ZC_MC,a.ZJZ from dw_ryxx_v a" +
					" where a.ryid in ( select ryid from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' and bz=3 ) " +
					" or a.ryid in ( select ryid from jfhsb_ajf where sbdm='"+dm+"0002' and dwid='"+dwid+"' and bz=3)  ";
			List<Map<String,Object>> listry = this.getSimpleJdbcTemplate().queryForList(sql);
			
			//资助
			sql = "select a.RYID,a.XM,a.SFZ,a.SEX_MC,isnull(a.XL_MC,' ') XL_MC,a.ZC_MC,a.ZJZ,b.DWID as DC,convert(numeric(18,2),b.SHZZBZ) SHZZBZ , convert(numeric(18,2),b.SHZCSJ) SHZCSJ," +
				"convert(numeric(18,2), b.SHZZZE) SHZZZE, isnull(b.ZFZFD,0) ZFZFD, convert(numeric(18,2), b.ZFBTBZ) ZFBTBZ ,convert(numeric(18,2), b.ZFZCSJ) ZFZCSJ," +
				"convert(numeric(18,2),  b.ZFBTZE) ZFBTZE," +
					" isnull(b.ZFZFD2,0) ZFZFD2,convert(numeric(18,2), b.ZFBTBZ2) ZFBTBZ2,convert(numeric(18,2),  b.ZFZCSJ2) ZFZCSJ2,convert(numeric(18,2), b.ZFBTZE2) ZFBTZE2," +
					" b.ZE, b.BZ,convert(numeric(18,2),(isnull(SHZZZE,0)+isnull(ZFBTZE,0)+isnull(ZFBTZE2,0))) ZZE  from dw_ryxx_v a , " +
					" ( select * from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' and bz=3 ) b where a.ryid=b.ryid " +
							"  and  a.dwid='"+dwid+"' and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (1,2)) ";
			List<Map<String,Object>> listzz = this.getSimpleJdbcTemplate().queryForList(sql);
			//安家费
			sql = "select a.RYID,a.XM,a.SFZ,a.SEX_MC,isnull(a.XL_MC,' ') XL_MC,a.ZC_MC,a.ZJZ, convert(numeric(18,2),(isnull(ZZBZ,0))) ZZBZ " +
				" from dw_ryxx_v a , " +
				" ( select * from jfhsb_ajf where sbdm='"+dm+"0002' and dwid='"+dwid+"' ) b where a.ryid=b.ryid " +
				"  and  a.dwid='"+dwid+"'  and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (3))  ";
		    List<Map<String,Object>> listajf =  this.getSimpleJdbcTemplate().queryForList(sql);
		    
		    
	    	for(int i=0;i<listry.size();i++){
	    		
	    		++row; mr = 0;
	    		labelC = new Label(mr,row,String.valueOf(i+1),wcfFCC);
		    	ws.addCell(labelC);
		    	
		    	labelC = new Label(++mr,row,String.valueOf(listry.get(i).get("XM")),wcfFCC);
		    	ws.addCell(labelC);
		    	
		    	labelC = new Label(++mr,row,String.valueOf(listry.get(i).get("XL_MC")),wcfFCC);
		    	ws.addCell(labelC);
		    	
		    	labelC = new Label(++mr,row,String.valueOf(listry.get(i).get("ZJZ")).equals("2")?"兼职":"专职",wcfFCC);
		    	ws.addCell(labelC);
		    	
		    	for(int j=0;j<listzz.size();j++){
		    		if(String.valueOf(listry.get(i).get("RYID")).equals(String.valueOf(listzz.get(j).get("RYID")))){

				    	labelC = new Label(++mr,row,String.valueOf(listzz.get(j).get("SHZZBZ")),wcfFCC);
				    	ws.addCell(labelC);

				    	labelC = new Label(++mr,row,String.valueOf(listzz.get(j).get("SHZCSJ")),wcfFCC);
				    	ws.addCell(labelC);

				    	labelC = new Label(++mr,row,String.valueOf(listzz.get(j).get("SHZZZE")),wcfFCC);
				    	ws.addCell(labelC);
				    	
				    	String  ZFZFD = "";
				    	String  ZFBTBZ = "";
				    	String  ZFZCSJ = "";
				    	String  ZFBTZE = "";
				    	if(String.valueOf(listzz.get(j).get("ZFZFD")).equals("1")){
				    		ZFZFD = "城内";
				    		ZFBTBZ = String.valueOf(listzz.get(j).get("ZFBTBZ"));
				    		ZFZCSJ = String.valueOf(listzz.get(j).get("ZFZCSJ"));
				    		ZFBTZE = String.valueOf(listzz.get(j).get("ZFBTZE"));
				    	}else{
				    		ZFZFD = "无";
				    		ZFBTBZ = "0";
				    		ZFZCSJ = "0";
				    		ZFBTZE = "0";				    		
				    	}
				    	
				    	if(String.valueOf(listzz.get(j).get("ZFZFD2")).equals("1")){
				    		ZFZFD += " / 城外";
				    		ZFBTBZ += " / "+String.valueOf(listzz.get(j).get("ZFBTBZ2"));
				    		ZFZCSJ += " / "+String.valueOf(listzz.get(j).get("ZFZCSJ2"));
				    		ZFBTZE += " / "+String.valueOf(listzz.get(j).get("ZFBTZE2"));
				    	}else{
				    		if(ZFZFD.equals("城内")){
				    			ZFZFD = ZFZFD+" / 无";
					    		ZFBTBZ += " / 0";
					    		ZFZCSJ += " / 0";
					    		ZFBTZE += " / 0";					    			
				    		}
				    	}
				    	labelC = new Label(++mr,row,ZFZFD,wcfFCC);
				    	ws.addCell(labelC);
				    	
				    	labelC = new Label(++mr,row,ZFBTBZ,wcfFCC);
				    	ws.addCell(labelC);
				    	
				    	labelC = new Label(++mr,row,ZFZCSJ,wcfFCC);
				    	ws.addCell(labelC);

				    	labelC = new Label(++mr,row,ZFBTZE,wcfFCC);
				    	ws.addCell(labelC);
				    	
//				    	labelC = new Label(12,row,String.valueOf(listzz.get(j).get("ZZE")),wcfFCC);
//				    	ws.addCell(labelC);
				    	
				    	boolean hasAJF = false;
				    	for(int jj=0;jj<listajf.size();jj++){
				    		if(String.valueOf(listry.get(i).get("RYID")).equals(String.valueOf(listajf.get(jj).get("RYID")))){
				    			hasAJF = true;
						    	labelC = new Label(11,row,String.valueOf(listajf.get(jj).get("ZZBZ")),wcfFCC);
						    	ws.addCell(labelC);
						    	
						    	Float hj = Float.valueOf( String.valueOf( listajf.get(jj).get("ZZBZ") ) ) 
						    				+ Float.valueOf( String.valueOf( listzz.get(j).get("ZZE") ) ) ;						    	
						    	
						    	labelC = new Label(12,row,String.valueOf( hj ),wcfFCC);
						    	ws.addCell(labelC);							    	
						    }
				    	}		    	
				    	if( !hasAJF ){
					    	labelC = new Label(11,row,"",wcfFCC);
					    	ws.addCell(labelC);
					    	labelC = new Label(12,row,String.valueOf(listzz.get(j).get("ZZE")),wcfFCC);
					    	ws.addCell(labelC);					    	
				    	}				    	
				    	
				    	
		    		}
		    	}
		    	
//		    	boolean hasAJF = false;
//		    	for(int j=0;j<listajf.size();j++){
//		    		if(String.valueOf(listry.get(i).get("RYID")).equals(String.valueOf(listajf.get(j).get("RYID")))){
//		    			hasAJF = true;
//				    	labelC = new Label(11,row,String.valueOf(listajf.get(j).get("ZZBZ")),wcfFCC);
//				    	ws.addCell(labelC);
//				    }
//		    	}		    	
//		    	if( !hasAJF ){
//			    	labelC = new Label(11,row,"",wcfFCC);
//			    	ws.addCell(labelC);
//		    	}
		    	
	    	}
	    	
	    	
	    	sql = "select sum(isnull(b.SHZZZE,0)) from dw_ryxx_v a , " +
			" ( select * from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' and bz=3 ) b where a.ryid=b.ryid " +
					"  and  a.dwid='"+dwid+"' and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (1,2)) ";
	    	String shzzze = this.getSimpleJdbcTemplate().queryForObject(sql, String.class);
	    	
	    	sql = "select sum(isnull(b.ZFBTZE,0))+sum(isnull(b.ZFBTZE2,0)) from dw_ryxx_v a , " +
			" ( select * from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' and bz=3 ) b where a.ryid=b.ryid " +
					"  and  a.dwid='"+dwid+"' and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (1,2)) ";
	    	String zfbtze = this.getSimpleJdbcTemplate().queryForObject(sql, String.class);
	    	
	    	
	    	sql = "select sum(isnull(ZZBZ,0))" +
			" from dw_ryxx_v a , " +
			" ( select * from jfhsb_ajf where sbdm='"+dm+"0002' and dwid='"+dwid+"' and bz=3 ) b where a.ryid=b.ryid " +
			"  and  a.dwid='"+dwid+"'  and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (3))  ";
	    	String ajfzz = this.getSimpleJdbcTemplate().queryForObject(sql, String.class);
	    	
	    	++row;
	    	ws.mergeCells(0, row, 12, row); //字段名
	    	if( shzzze == null ){ shzzze = "0"; } //生活补助合计	
	    	if( zfbtze == null ){ zfbtze = "0"; } //住房补助合计
	    	if( ajfzz == null ){ ajfzz = "0"; }	  //安家费补助合计
	    	String zzze = String.valueOf(
	    			  Float.valueOf(shzzze)
	    			+ Float.valueOf(zfbtze)
	    			+ Float.valueOf(ajfzz)
	    	);
	    	
	    	labelC = new Label(0,row,"资助总额合计："+zzze+"；其中：生活补助合计:"+shzzze+";住房补助合计:"+zfbtze+";安家费合计："+ajfzz,wcfFCC);
	    	ws.addCell(labelC);
	    	
	    	++row;
	    	ws.mergeCells(0, row, 12, row); //字段名
	    	labelC = new Label(0,row,"                                                                                                                                                                           单位领导签字:",wcfFCC);
	    	ws.addCell(labelC);
	    	
	    	
	    	wwb.write();
	    	wwb.close();
		}catch(Exception e){
			e.printStackTrace();
			throw  new BusException("导出出现异常!");
		}
		
	}
}
